<?php
/**
 * Created by PhpStorm.
 * User: zcq
 * Date: 2019/9/25
 * Time: 10:15
 */

namespace app\admin\controller;


use think\Db;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class LotteryData extends Base
{
    public $table = 'lottery_data';

    public function index()
    {
        $keyword = input('keyword');
        $prize_id = input('prize_id');
        $lottery_id = input('lottery_id');

        if($prize_id){
            $where['a.prize_id'] = ['eq',$prize_id];
        }

        if($keyword)
            $where['a.mobile|a.link_name|a.link_tel|a.link_address'] = ['like','%'.$keyword.'%'];

        if(!isset($where))
            $where = '1=1';


        $data['list'] = Db::name($this->table)
            ->alias('a')
            ->join('lottery_prize b','b.id=a.prize_id')
            ->where('a.site_id',$this->site_id)
            ->where('a.lottery_id',$lottery_id)
            ->where($where)
            ->order('a.id desc')
            ->field('a.*,b.name')
            ->paginate();

        $data['page'] = $data['list']->render();

        $prize = Db::name('lottery_prize')->where('lottery_id',$lottery_id)->select();
        $this->assign('prize',$prize);
        $this->assign('prize_id',$prize_id);
        $this->assign('lottery_id',$lottery_id);
        $this->assign('data',$data);
        $this->assign('keyword',$keyword);
        return $this->fetch();
    }

    /**
     * @return mixed
     * 统计
     */
    public function count()
    {
        $lotteryId = input('lottery_id/d');
        $lottery = $this->checkLottery($lotteryId);
        if(!$lottery)
            $this->error('不存在');
        $order = input('order');
        $start = input('start');
        $end = input('end');
        $keyword = input('keyword');
        $itemId = input('item');
        $items = DB::name('lottery_prize')->where('lottery_id', $lotteryId)->select();
        //echarts 按每日投票统计数据

        $data = $this->countData($lottery, $start, $end, $order, $keyword, $itemId);

        $this->assign('data',$data);
        $this->assign('lottery',$lottery);
        $this->assign('start',$start?:'');
        $this->assign('end',$end?:'');
        $this->assign('order',$order?:'');
        $this->assign('keyword',$keyword);
        $this->assign('item',$itemId);
        $this->assign('items', $items);
        $this->assign('chartData',json_encode($data['chartData']));
        trace($data['chartData'], 'error');
        return $this->fetch();
    }

    public function countData($lottery, $start, $end, $order, $keyword, $itemId) {

        $where['c_lottery_data.lottery_id'] = $where1['c_lottery_data.lottery_id'] = ['eq',$lottery['id']];

        if($itemId)
            $where['prize_id'] = $where1['prize_id'] = ['eq',$itemId];

        if($keyword){
            $where['mobile'] = $where1['mobile'] = ['eq',$keyword];
        }
        if ($start && $end) {
            $where['c_lottery_data.addtime'] = ['between', [strtotime($start), strtotime($end)]];
        } else if ($start) {
            $where['c_lottery_data.addtime'] = ['between', [strtotime($start), $lottery['end']]];
            // $where['c_lottery_data.addtime'] = ['>=', strtotime($start)];
        } else if ($end) {
            $where['c_lottery_data.addtime'] = ['between', [$lottery['start'], strtotime($end)]];
            // $where['c_lottery_data.addtime'] = ['<=', strtotime($end)];
        } else {
            $where['c_lottery_data.addtime'] = ['between', [$lottery['start'], $lottery['end']]];
        }
        if($order=='asc'){
            $o = 'total asc';
        }elseif($order == 'desc'){
            $o = 'total desc';
        }else{
            $o = '';
        }

        $data = [];
        $data['list'] = Db::name("lottery_data")
            ->join('lottery_prize b','b.id=c_lottery_data.prize_id')
            ->where($where)
            ->field('mobile, count(c_lottery_data.id) as total')
            ->group('mobile')
            ->order($o)
            ->paginate();
        $data['page'] = $data['list']->render();
        $data['chartData'] = [];
        $time = strtotime($start)?: $lottery['start'];
        $endtime = strtotime($end) ?: $lottery['end'];
        $i = 0;
        $category = [];
        $total = [];
        while (true){
            $now = $time + $i*3600*24;
            $next = $time + ($i+1)*3600*24 - 1;
            if($now > $endtime)
                break;

            $nums = DB::name('lottery_data')
                ->join('lottery_prize b','b.id=c_lottery_data.prize_id')
                ->where($where1)
                ->where('c_lottery_data.addtime','between',[$now,$next])
                ->count('c_lottery_data.id')?:0;

            $category[] = date('Y-m-d',$now);
            $total[] = $nums;
            $i++;
        }
        $data['chartData']['category'] = $category;
        $data['chartData']['total'] = $total;
        $data['total'] = array_sum($total);
        return $data;
    }

    public function countList($lotteryId,$itemId)
    {
        $where['c_lottery_data.lottery_id'] = ['eq',$lotteryId];
        if($itemId)
            $where['prize_id'] = ['eq',$itemId];

        $list = Db::name("lottery_data")
            ->join('lottery_prize b','b.id=c_lottery_data.prize_id')
            ->where($where)
            ->field('mobile, count(*) as total')
            ->group('mobile')
            ->order('total desc')
            ->select();
        return $list;
    }

    /**
     * @return mixed
     * 某个手机的抽奖记录
     */
    public function infoByMobile()
    {
        $id = input('lottery_id/d');
        $lottery = $this->checkLottery($id);
        $mobile = input('mobile');
//        $data = $this->dataService->countByMobile($id,$mobile);

        $data['list'] = Db::name('lottery_data')
            ->alias('a')
            ->join('lottery_prize b','b.id=a.prize_id')
            ->where('a.lottery_id',$id)
            ->where('a.mobile',$mobile)
            ->field('count(a.id) as total,b.name,b.id')
            ->group('a.prize_id')
            ->paginate();
        $data['page'] = $data['list']->render();

        $this->assign('data',$data);
        $this->assign('lottery',$lottery);
        $this->assign('mobile',$mobile);
        return $this->fetch();
    }

    public function downloadCount()
    {
        $lottery_id = input('lottery_id/d');
        $lottery = $this->checkLottery($lottery_id);
        $start = input('start');
        $end = input('end');
        $keyword = input('keyword');
        $itemId = input('item');

        $list = $this->countList($lottery_id,$itemId);

        $speadSheet = new Spreadsheet();
        $speadSheet->setActiveSheetIndex(0);
        $speadSheet->getActiveSheet()
            ->setCellValue('A1', '手机号码')
            ->setCellValue('B1', '投票数');

        if ($list) {
            foreach ($list as $k => $v) {
                $speadSheet->getActiveSheet()
                    ->setCellValueExplicit('A' . ($k + 2), $v['mobile'],DataType::TYPE_STRING)
                    ->setCellValue('B' . ($k + 2), $v['total']);
            }
        }
        $filename = $lottery['name'] . '.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($speadSheet, 'Xlsx');
        $writer->save('php://output');
    }


    public function checkLottery($lottery_id = '')
    {
        if (!$lottery_id)
            $lottery_id = input('lottery_id');
        $lottery = Db::name('lottery')->where('id', $lottery_id)->where('site_id', $this->site_id)->find();
        if (!$lottery)
            return $this->error('活动不存在');
        $this->assign('lottery', $lottery);
        $this->assign('lottery_json', json_encode($lottery, JSON_UNESCAPED_UNICODE));
        return $lottery;
    }
}